package com.pa.middle.opr.job.core.utils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import com.pa.middle.opr.trade.util.PropertyUtil;


public class  ExcelUtils {
    /**
     * 有时间记得重构下
	 * 生成excel流 
     * @throws IOException 
     * @param type xone-1 lr-2 
	 */
	public static String createExcelIO(List<ExcelSheetUtils> sheets, String bgnDate,String orgCode,String type) throws IOException{
		ByteArrayOutputStream bos=null;
		InputStream input=null;
		String paramString="";
		XSSFWorkbook wb = getExcel(sheets, bgnDate,orgCode,type);	     
		bos = new ByteArrayOutputStream();
		wb.write(bos); // 写入bate对象
		byte[] byteArray = bos.toByteArray();
		input = new ByteArrayInputStream(byteArray);
		paramString = streamToString(input);

		if (bos != null) {
			bos.close();// 操作结束，关闭文件
		}
		if (input != null) {
			input.close();// 操作结束，关闭文件
		}

		return paramString;
	}
	   /**
	   * 生产excel文件
	   * @param sheets
	   * @param bgnDate
	   * @param xls
	   * @param type xone-1 lr-2
	   * @return
	   * @throws IOException
	   */
	public static String createExcel(List<ExcelSheetUtils> sheets, String bgnDate,String xls,String orgCode,String type) throws IOException{
		OutputStream os = null;
		 XSSFWorkbook wb = getExcel(sheets, bgnDate,orgCode,type);
		 String filePath = getFilePath(xls);
		 os = new FileOutputStream(filePath);
		 wb.write(os);
         os.flush();
         if (os!=null) {
        	 os.close();	
		}
         return filePath;
	}
	/**
	 * 生产excel对象
	 * @param sheets
	 * @param bgnDate
	 * @return
	 */
	private static XSSFWorkbook getExcel(List<ExcelSheetUtils> sheets, String bgnDate,String orgCode,String type){
		if (type.equals("1")) {			
		
		//创建excel
		XSSFWorkbook wb = new XSSFWorkbook();
		
		//设置excel样式
		XSSFCellStyle headStyle = wb.createCellStyle();
		XSSFCellStyle headSmallStyle = wb.createCellStyle();
		XSSFCellStyle titleStyle = wb.createCellStyle();
		XSSFCellStyle contentStyle = wb.createCellStyle();
		XSSFCellStyle contentStyleRed = wb.createCellStyle();		
		XSSFDataFormat format = wb.createDataFormat();
		
        //内容
		XSSFFont fontContent = wb.createFont();
		fontContent.setFontName("Arial"); // 什么字体
		fontContent.setFontHeightInPoints((short)9);
		contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		contentStyle.setDataFormat(format.getFormat("TEXT"));
		contentStyle.setWrapText(true);
		contentStyle.setFont(fontContent);
		
		//红色字体
		XSSFFont fontred = wb.createFont();
		fontred.setColor(HSSFColor.RED.index);
		fontred.setFontName("Arial"); // 什么字体
		fontred.setFontHeightInPoints((short)9);
		contentStyleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		contentStyleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
		contentStyleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
		contentStyleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		contentStyleRed.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		contentStyleRed.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		contentStyleRed.setDataFormat(format.getFormat("TEXT"));
		contentStyleRed.setWrapText(true);
		contentStyleRed.setFont(fontred);
		
		//头部
		XSSFFont fontHead = wb.createFont();
		fontHead.setFontHeightInPoints((short) 12); // 字体大小
		fontHead.setFontName("Arial"); // 什么字体
		fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗		
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headStyle.setDataFormat(format.getFormat("TEXT"));
		headStyle.setWrapText(true);
		headStyle.setFont(fontHead);
		
		//头部小字
		XSSFFont fontHeadSmall = wb.createFont();
		fontHeadSmall.setFontHeightInPoints((short) 9); // 字体大小
		fontHeadSmall.setFontName("Arial"); // 什么字体
		fontHeadSmall.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗		
		headSmallStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headSmallStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headSmallStyle.setDataFormat(format.getFormat("TEXT"));
		headSmallStyle.setWrapText(true);
		headSmallStyle.setFont(fontHeadSmall);
			

		//标题
		XSSFFont fontTitle = wb.createFont();
		fontTitle.setFontHeightInPoints((short) 9); // 字体大小
		fontTitle.setFontName("Arial"); // 什么字体
		fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
		titleStyle.cloneStyleFrom(contentStyle);
		titleStyle.setFont(fontTitle);
		
     for (ExcelSheetUtils esu : sheets) {
    	 String[] title = esu.getTitle();
    	 String[] contentKey = esu.getContentKey();
    	 String sheet =esu.getSheet();
    	 List<Map> list = esu.getContent();
    	 //如果大标题不为空,那么就是第一页
	if (!StringUtils.isEmpty(esu.getHeardTitle())) {
		XSSFSheet st = wb.createSheet(sheet);
 		//设置列宽
		st.setDefaultRowHeightInPoints(15);//这是设置整个列表的高度高
		st.setDefaultColumnWidth(15);//设置整个列表的宽度

		int length = title.length;

		XSSFRow hrow = st.createRow(0);
		XSSFCell titleCell = hrow.createCell(0);
		titleCell.setCellValue(esu.getHeardTitle());
		titleCell.setCellStyle(headStyle);
		//参数说明：1：开始行 2：结束行  3：开始列 4：结束列
		st.addMergedRegion(new CellRangeAddress(0,0,0,length - 1));
		
		XSSFRow hrow1 = st.createRow(1);
		
		XSSFRow hrow2 = st.createRow(2);
		XSSFCell Cell2 = hrow2.createCell(0);
		Cell2.setCellValue("制表:"+orgCode);
		Cell2.setCellStyle(headSmallStyle);
		st.addMergedRegion(new CellRangeAddress(2,2,0,length - 1));
		
		XSSFRow hrow3 = st.createRow(3);
		XSSFCell Cel3 = hrow3.createCell(0);
		Cel3.setCellValue("开始日期:"+bgnDate+"结束日期:"+bgnDate);
		Cel3.setCellStyle(headSmallStyle);
		st.addMergedRegion(new CellRangeAddress(3,3,0,length - 1));
		
		XSSFRow hrow4 = st.createRow(4);
		XSSFCell title2Cell=hrow4.createCell(0);
		title2Cell.setCellValue(sheet);
		title2Cell.setCellStyle(headStyle);
		st.addMergedRegion(new CellRangeAddress(4,4,0,length - 1));
		
		XSSFRow hrow5 = st.createRow(5);
		for (int i = 0; i < length; i++) {

			XSSFCell cell = hrow5.createCell(i);
			cell.setCellValue(new XSSFRichTextString(title[i]));
			cell.setCellStyle(titleStyle);

		}

		if (list != null && list.size() > 0) {

			int size = list.size();
			for (int i = 0; i < size; i++) {
				XSSFRow row = st.createRow(i + 6);
				Map dataRow = (Map) list.get(i);
				for (int j = 0; j < contentKey.length; j++) {
					XSSFCell cell = row.createCell(j);
					Object eleData = dataRow.get(contentKey[j]);
					if (StringUtils.isEmpty(eleData)) {
						cell.setCellValue("");
						cell.setCellStyle(contentStyle);
					}else{
						String value =eleData.toString();
						cell.setCellValue(value);
						if (value.startsWith("-")) {								
							cell.setCellStyle(contentStyleRed);
						}else{
							cell.setCellStyle(contentStyle);
						}
					}
				
				}
			}

		}
	
     }else{
    	 
    	    XSSFSheet st = wb.createSheet(esu.getSheet());
			st.setDefaultRowHeightInPoints(15);//这是设置整个列表的高度高
			st.setDefaultColumnWidth(10);//设置整个列表的宽度
			int length = title.length;
			
			XSSFRow hrow = st.createRow(0);			
			XSSFRow hrow2 = st.createRow(1);
			for (int i = 0; i < length; i++) {

				XSSFCell cell = hrow2.createCell(i);
				cell.setCellValue(new XSSFRichTextString(title[i]));
				cell.setCellStyle(titleStyle);

			}

			if (list != null && list.size() > 0) {

				int size = list.size();
				for (int i = 0; i < size; i++) {
					XSSFRow row = st.createRow(i + 2);
					Map dataRow = (Map) list.get(i);
					for (int j = 0; j < contentKey.length; j++) {
						XSSFCell cell = row.createCell(j);
						Object eleData = dataRow.get(contentKey[j]);

						if (StringUtils.isEmpty(eleData)) {
							cell.setCellValue("");
							cell.setCellStyle(contentStyle);
						}else{
							String value =eleData.toString();
							cell.setCellValue(value);
							if (value.startsWith("-")) {								
								cell.setCellStyle(contentStyleRed);
							}else{
								cell.setCellStyle(contentStyle);
							}
						}
					
					}
				}

			}
			
     }	
	 }

     //创建页尾
     XSSFSheet st = wb.createSheet("页尾");
	 st.setDefaultRowHeightInPoints(15);//这是设置整个列表的高度高
     st.setDefaultColumnWidth(100);//设置整个列表的宽度
     XSSFRow hrow = st.createRow(0);			
 	 XSSFRow hrow2 = st.createRow(1);
 	 XSSFCell cell = hrow2.createCell(0);
	 cell.setCellValue(new SimpleDateFormat("制表日期:"+"yyyy年  MM月 dd日    HH点  mm分 ss秒"+"      操作员："+orgCode).format(new Date()));
	 cell.setCellStyle(titleStyle);
	 
	 return wb;
		} else {
			// 创建excel 两融
			XSSFWorkbook wb = new XSSFWorkbook();

			// 设置excel样式
			XSSFCellStyle headStyle = wb.createCellStyle();
			XSSFCellStyle headSmallStyle = wb.createCellStyle();
			XSSFCellStyle titleStyle = wb.createCellStyle();
			XSSFCellStyle contentStyle = wb.createCellStyle();
			XSSFCellStyle contentStyleRed = wb.createCellStyle();
			XSSFDataFormat format = wb.createDataFormat();

			// 内容
			XSSFFont fontContent = wb.createFont();
			fontContent.setFontName("Arial"); // 什么字体
			fontContent.setFontHeightInPoints((short) 9);
			contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			contentStyle.setDataFormat(format.getFormat("TEXT"));
			contentStyle.setWrapText(true);
			contentStyle.setFont(fontContent);

			// 红色字体
			XSSFFont fontred = wb.createFont();
			fontred.setColor(HSSFColor.RED.index);
			fontred.setFontName("Arial"); // 什么字体
			fontred.setFontHeightInPoints((short) 9);
			contentStyleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			contentStyleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
			contentStyleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
			contentStyleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			contentStyleRed.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			contentStyleRed.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			contentStyleRed.setDataFormat(format.getFormat("TEXT"));
			contentStyleRed.setWrapText(true);
			contentStyleRed.setFont(fontred);

			// 头部
			XSSFFont fontHead = wb.createFont();
			fontHead.setFontHeightInPoints((short) 12); // 字体大小
			fontHead.setFontName("Arial"); // 什么字体
			fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			headStyle.setDataFormat(format.getFormat("TEXT"));
			headStyle.setWrapText(true);
			headStyle.setFont(fontHead);

			// 头部小字
			XSSFFont fontHeadSmall = wb.createFont();
			fontHeadSmall.setFontHeightInPoints((short) 9); // 字体大小
			fontHeadSmall.setFontName("Arial"); // 什么字体
			fontHeadSmall.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
			headSmallStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headSmallStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			headSmallStyle.setDataFormat(format.getFormat("TEXT"));
			headSmallStyle.setWrapText(true);
			headSmallStyle.setFont(fontHeadSmall);

			// 标题
			XSSFFont fontTitle = wb.createFont();
			fontTitle.setFontHeightInPoints((short) 9); // 字体大小
			fontTitle.setFontName("Arial"); // 什么字体
			fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
			titleStyle.cloneStyleFrom(contentStyle);
			titleStyle.setFont(fontTitle);

			for (ExcelSheetUtils esu : sheets) {
				String[] title = esu.getTitle();
				String[] contentKey = esu.getContentKey();
				String sheet = esu.getSheet();
				List<Map> list = esu.getContent();
				// 如果大标题不为空,那么就是第一页
				if (!StringUtils.isEmpty(esu.getHeardTitle())) {
					XSSFSheet st = wb.createSheet(sheet);
					// 设置列宽
					st.setDefaultRowHeightInPoints(17);// 这是设置整个列表的高度高
					st.setDefaultColumnWidth(15);// 设置整个列表的宽度

					int length = title.length;

					XSSFRow hrow0 = st.createRow(0);
					st.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
					XSSFRow hrow1 = st.createRow(1);
					XSSFCell titleCell = hrow1.createCell(0);
					titleCell.setCellValue(esu.getHeardTitle());
					titleCell.setCellStyle(headStyle);
					// 参数说明：1：开始行 2：结束行 3：开始列 4：结束列
					st.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));

					XSSFRow hrow2 = st.createRow(2);
					XSSFCell Cell2 = hrow2.createCell(0);
					Cell2.setCellValue("帐单日期:    " + bgnDate + "--" + bgnDate);
					Cell2.setCellStyle(headSmallStyle);
					st.addMergedRegion(new CellRangeAddress(2, 2, 0, 5));

					XSSFRow hrow3 = st.createRow(3);
					XSSFCell title2Cell = hrow3.createCell(0);
					title2Cell.setCellValue(sheet);
					title2Cell.setCellStyle(headStyle);
					st.addMergedRegion(new CellRangeAddress(3, 3, 0, 5));
					// 基本信息值
					Map dataRow = (Map) list.get(0);

					List<Map> listMap = new ArrayList<>();
					for (int i = 0; i < length; i = i + 2) {
						Map map = new HashMap<>();
						map.put("1", title[i] + ";" + contentKey[i]);
						map.put("2", title[i + 1] + ";" + contentKey[i + 1]);
						listMap.add(map);
					}
					int size2 = listMap.size();
					for (int i = 0; i < size2; i++) {
						Map map = listMap.get(i);
						XSSFRow row = st.createRow(4 + i);
						XSSFCell cell0 = row.createCell(0);
						cell0.setCellStyle(contentStyle);
						cell0.setCellValue(map.get("1").toString().split(";")[0]);
						XSSFCell cell1 = row.createCell(1);
						XSSFCell cell2 = row.createCell(2);
						Object userName = dataRow.get(map.get("1").toString().split(";")[1]);
						cell1.setCellStyle(contentStyle);
						cell2.setCellStyle(contentStyle);
						cell1.setCellValue(StringUtils.isEmpty(userName) ? "" : userName.toString());
						st.addMergedRegion(new CellRangeAddress(4 + i, 4 + i, 1, 2));
						XSSFCell cell3 = row.createCell(3);
						cell3.setCellStyle(contentStyle);
						cell3.setCellValue(map.get("2").toString().split(";")[0]);
						XSSFCell cell4 = row.createCell(4);
						XSSFCell cell5 = row.createCell(5);
						Object account = dataRow.get(map.get("2").toString().split(";")[1]);
						cell4.setCellStyle(contentStyle);
						cell4.setCellValue(StringUtils.isEmpty(account) ? "" : account.toString());
						cell5.setCellStyle(contentStyle);
						st.addMergedRegion(new CellRangeAddress(4 + i, 4 + i, 4, 5));
					}

				} else if (esu.getSheet().equals("净资产") || esu.getSheet().equals("当前资产")) {

					XSSFSheet st = wb.createSheet(esu.getSheet());
					st.setDefaultRowHeightInPoints(15);// 这是设置整个列表的高度高
					st.setDefaultColumnWidth(10);// 设置整个列表的宽度
					int length = title.length;

					XSSFRow hrow = st.createRow(0);
					XSSFRow hrow2 = st.createRow(1);
					for (int i = 0; i < length; i++) {

						XSSFCell cell = hrow2.createCell(i);
						cell.setCellValue(new XSSFRichTextString(title[i]));
						cell.setCellStyle(titleStyle);

					}

					if (list != null && list.size() > 0) {

						int size = list.size();
						for (int i = 0; i < size; i++) {
							XSSFRow row = st.createRow(i + 2);
							Map dataRow = (Map) list.get(i);
							for (int j = 0; j < contentKey.length; j++) {
								XSSFCell cell = row.createCell(j);
								Object eleData = dataRow.get(contentKey[j]);

								if (StringUtils.isEmpty(eleData)) {
									cell.setCellValue("");
									cell.setCellStyle(contentStyle);
								} else {
									String value = eleData.toString();
									cell.setCellValue(value);
									if (value.startsWith("-")) {
										cell.setCellStyle(contentStyleRed);
									} else {
										cell.setCellStyle(contentStyle);
									}
								}

							}
						}

					}
				} else if (esu.getSheet().equals("负债情况") || esu.getSheet().equals("负债明细")
						|| esu.getSheet().equals("业务流水")) {

					XSSFSheet st = wb.createSheet(esu.getSheet());
					st.setDefaultRowHeightInPoints(15);// 这是设置整个列表的高度高
					st.setDefaultColumnWidth(10);// 设置整个列表的宽度
					int length = title.length;

					XSSFRow hrow0 = st.createRow(0);
					st.addMergedRegion(new CellRangeAddress(0, 0, 0, length - 1));
					XSSFRow hrow1 = st.createRow(1);
					XSSFCell Cell1 = hrow1.createCell(0);
					Cell1.setCellValue(esu.getSheet());
					Cell1.setCellStyle(headSmallStyle);
					st.addMergedRegion(new CellRangeAddress(1, 1, 0, length - 1));
					XSSFRow hrow2 = st.createRow(2);
					for (int i = 0; i < length; i++) {

						XSSFCell cell = hrow2.createCell(i);
						cell.setCellValue(new XSSFRichTextString(title[i]));
						cell.setCellStyle(titleStyle);

					}

					if (list != null && list.size() > 0) {

						int size = list.size();
						for (int i = 0; i < size; i++) {
							XSSFRow row = st.createRow(i + 3);
							Map dataRow = (Map) list.get(i);
							for (int j = 0; j < contentKey.length; j++) {
								XSSFCell cell = row.createCell(j);
								Object eleData = dataRow.get(contentKey[j]);

								if (StringUtils.isEmpty(eleData)) {
									cell.setCellValue("");
									cell.setCellStyle(contentStyle);
								} else {
									String value = eleData.toString();
									cell.setCellValue(value);
									if (value.startsWith("-")) {
										cell.setCellStyle(contentStyleRed);
									} else {
										cell.setCellStyle(contentStyle);
									}
								}

							}
						}

					}

				} else if (esu.getSheet().equals("资产负债情况")) {

					XSSFSheet st = wb.createSheet(esu.getSheet());
					st.setDefaultRowHeightInPoints(15);// 这是设置整个列表的高度高
					st.setDefaultColumnWidth(10);// 设置整个列表的宽度
					int length = title.length;

					XSSFRow hrow0 = st.createRow(0);
					st.addMergedRegion(new CellRangeAddress(0, 0, 0, length - 1));
					XSSFRow hrow1 = st.createRow(1);
					XSSFCell Cell1 = hrow1.createCell(0);
					Cell1.setCellValue(esu.getSheet());
					Cell1.setCellStyle(headStyle);
					st.addMergedRegion(new CellRangeAddress(1, 1, 0, length - 1));
					XSSFRow hrow2 = st.createRow(2);
					XSSFCell Cell2 = hrow2.createCell(0);
					Cell2.setCellValue("1、资产情况");
					Cell2.setCellStyle(headSmallStyle);
					st.addMergedRegion(new CellRangeAddress(2, 2, 0, length - 1));
					XSSFRow hrow3 = st.createRow(3);
					XSSFCell Cell3 = hrow3.createCell(0);
					Cell3.setCellValue("1.1当前资产情况");
					Cell3.setCellStyle(headSmallStyle);
					st.addMergedRegion(new CellRangeAddress(3, 3, 0, length - 1));
					XSSFRow hrow4 = st.createRow(4);
					for (int i = 0; i < length; i++) {

						XSSFCell cell = hrow4.createCell(i);
						cell.setCellValue(new XSSFRichTextString(title[i]));
						cell.setCellStyle(titleStyle);

					}

					if (list != null && list.size() > 0) {

						int size = list.size();
						for (int i = 0; i < size; i++) {
							XSSFRow row = st.createRow(i + 5);
							Map dataRow = (Map) list.get(i);
							for (int j = 0; j < contentKey.length; j++) {
								XSSFCell cell = row.createCell(j);
								Object eleData = dataRow.get(contentKey[j]);

								if (StringUtils.isEmpty(eleData)) {
									cell.setCellValue("");
									cell.setCellStyle(contentStyle);
								} else {
									String value = eleData.toString();
									cell.setCellValue(value);
									if (value.startsWith("-")) {
										cell.setCellStyle(contentStyleRed);
									} else {
										cell.setCellStyle(contentStyle);
									}
								}

							}
						}

					}
				}
			}

			// 创建页尾
			XSSFSheet st = wb.createSheet("页尾");
			st.setDefaultRowHeightInPoints(15);// 这是设置整个列表的高度高
			st.setDefaultColumnWidth(100);// 设置整个列表的宽度
			XSSFRow hrow = st.createRow(0);
			XSSFRow hrow2 = st.createRow(1);
			XSSFCell cell = hrow2.createCell(0);
			cell.setCellValue(new SimpleDateFormat("制表日期:" + "yyyy年  MM月 dd日    HH点  mm分 ss秒" + "      操作员：" + orgCode)
					.format(new Date()));
			cell.setCellStyle(titleStyle);

			return wb;

		}
	}
	/**
	 * 将流转换成字符串 使用Base64加密
	 * 
	 * @param in输入流
	 * @return
	 * @throws IOException
	 */
	public static String streamToString(InputStream inputStream) throws IOException {
		byte[] bt = toByteArray(inputStream);
		String out = new sun.misc.BASE64Encoder().encodeBuffer(bt);
		return out;
	}

	/**
	 * 
	 * summary:将流转化为字节数组
	 * 
	 * @param inputStream
	 * @return
	 * @throws IOException
	 * 
	 */
	public static byte[] toByteArray(InputStream inputStream) throws IOException {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		byte[] buffer = new byte[1024 * 4];
		byte[] result = null;
		try {
			int n = 0;
			while ((n = inputStream.read(buffer)) != -1) {
				out.write(buffer, 0, n);
			}
			result = out.toByteArray();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			if (out!=null) {
				out.close();
			}
		}
		return result;
	}
	/**
	 * 获取系统路径
	 * @param xls
	 * @return
	 */
	public static String getFilePath(String xls) {
		String path = getHeaderRealPath();
		File file = new File(path);
		if (!file.exists()) {
			file.mkdirs();
		}
        
		return path+xls;
	}
    /**
     * 生产文件地址
     * @return
     */
	private static String getHeaderRealPath() {
		SimpleDateFormat dbf=new SimpleDateFormat("yyyy-MM-dd");
		String dateDZD=dbf.format(new Date())+"DZD";
		
		SimpleDateFormat dateFormat=new SimpleDateFormat("HH");
		String hh=dateFormat.format(new Date());
		
		SimpleDateFormat dateFormatmm=new SimpleDateFormat("mm");
		String mm=dateFormatmm.format(new Date());
		
		String rootPath = PropertyUtil.getValue("NAS_ROOTPATH");
		String directoryName = PropertyUtil.getValue("NAS_ID_VERIFY_IMG_DIRECTORY");
		if (rootPath.endsWith(File.separator)) {
			return rootPath + directoryName + System.getProperty("file.separator") +dateDZD+System.getProperty("file.separator")+ hh + System.getProperty("file.separator") + mm + System.getProperty("file.separator");
		} else {
			return rootPath  + directoryName + System.getProperty("file.separator") +dateDZD+System.getProperty("file.separator")+ hh + System.getProperty("file.separator") + mm + System.getProperty("file.separator");
		}
	}
    /**
     * 读取文件
     * @param url
     * @return
     * @throws IOException
     */
	public static String inputExcel(String url) throws IOException{
		FileInputStream  input=new FileInputStream(url);		
	    String	paramString = streamToString(input);
		if (input != null) {
			input.close();// 操作结束，关闭文件
		}
		return paramString;
	}
    /**
     * 删除文件
     */
	public static void deleteFileExcels() {
		SimpleDateFormat dbf=new SimpleDateFormat("yyyy-MM-dd");
		String dateDZD=dbf.format(new Date())+"DZD";
		String rootPath = PropertyUtil.getValue("NAS_ROOTPATH");
		String directoryName = PropertyUtil.getValue("NAS_ID_VERIFY_IMG_DIRECTORY");
		if (rootPath.endsWith(File.separator)) {
			 String url = rootPath + directoryName + System.getProperty("file.separator") +dateDZD+System.getProperty("file.separator");
			 deleteDirectory(url);
		} else {
			String url =rootPath  + directoryName + System.getProperty("file.separator") +dateDZD+System.getProperty("file.separator");
			 deleteDirectory(url);
		}
	}
	
	/**
	 * 删除目录（文件夹）以及目录下的文件
	 * 
	 * @param dir
	 *            被删除目录的文件路径
	 * @return 目录删除成功返回true,否则返回false
	 */
	public static boolean deleteDirectory(String dir) {
		// 如果dir不以文件分隔符结尾，自动添加文件分隔符
		if (!dir.endsWith(File.separator)) {
			dir = dir + File.separator;
		}
		File dirFile = new File(dir);
		// 如果dir对应的文件不存在，或者不是一个目录，则退出
		if (!dirFile.exists() || !dirFile.isDirectory()) {
			System.out.println("删除目录失败" + dir + "目录不存在！");
			return false;
		}
		boolean flag = true;
		// 删除文件夹下的所有文件(包括子目录)
		File[] files = dirFile.listFiles();
		for (int i = 0; i < files.length; i++) {
			// 删除子文件
			if (files[i].isFile()) {
				flag = deleteFile(files[i].getAbsolutePath());
				if (!flag) {
					break;
				}
			}
			// 删除子目录
			else {
				flag = deleteDirectory(files[i].getAbsolutePath());
				if (!flag) {
					break;
				}
			}
		}
 
		if (!flag) {
			System.out.println("删除目录失败");
			return false;
		}
 
		// 删除当前目录
		if (dirFile.delete()) {
			System.out.println("删除目录" + dir + "成功！");
			return true;
		} else {
			System.out.println("删除目录" + dir + "失败！");
			return false;
		}
	}

	/**
	 * 删除单个文件
	 * 
	 * @param fileName
	 *            被删除文件的文件名
	 * @return 单个文件删除成功返回true,否则返回false
	 */
	public static boolean deleteFile(String fileName) {
		File file = new File(fileName);
		if (file.isFile() && file.exists()) {
			file.delete();
			System.out.println("删除单个文件" + fileName + "成功！");
			return true;
		} else {
			System.out.println("删除单个文件" + fileName + "失败！");
			return false;
		}
	}

}
